<?php if(!defined('BASEPATH')) exit('No direct script allowed');



class mdl_piutang_pajak_per_wilayah extends CI_Model{

	

	function __construct()
	{
		parent::__construct();
	}
	
	
	function getItem($periode=0, $kode_wil=null, $jenis_kb=null)
	{ 
	
	if (!$periode){$periode = 0;}
	if (!$kode_wil){$kode_wil = 0;}
	if (!$jenis_kb){$jenis_kb = 0;}
		$query = "SELECT
					t_trnkb.kd_wilayah,
					t_wilayah.nm_wilayah,
					t_trnkb.kd_jenis_kb,
					t_jeniskb.uraian,
					sum(bea_pkb_pok) as pkb 
					FROM
					t_trnkb 
					left join t_wilayah on t_trnkb.kd_wilayah = t_wilayah.kd_wilayah  
					left join t_jeniskb on  t_trnkb.kd_jenis_kb = t_jeniskb.kode 
					where 
					bea_pkb_pok > 0

					and tg_tetap >= '01-01-$periode' 
					and tg_tetap <= '31-12-$periode'  
					";
		if (($kode_wil !='all'))
			{$query .= "and t_trnkb.kd_wilayah='$kode_wil'";}
		if (($jenis_kb !='all'))
			{$query .= "and t_trnkb.kd_jenis_kb='$jenis_kb'";}	
			$query	.="group by kd_wilayah,kd_jenis_kb,nm_wilayah,uraian";
		$this->db->flush_cache();
		echo $query;echo'------';
		
		return $this->db->query($query);
	}
	
	function getItemCount($periode=0, $kode_wil=null, $jenis_kb=null)
	{ 
	if (!$periode){$periode = 0;}
	if (!$kode_wil){$kode_wil = 0;}
	if (!$jenis_kb){$jenis_kb = 0;}
		$query = "SELECT
					t_trnkb.kd_wilayah,
					t_wilayah.nm_wilayah,
					t_trnkb.kd_jenis_kb,
					t_jeniskb.uraian,
					sum(bea_pkb_pok) as pkb 
					FROM
					t_trnkb 
					left join t_wilayah on t_trnkb.kd_wilayah = t_wilayah.kd_wilayah  
					left join t_jeniskb on  t_trnkb.kd_jenis_kb = t_jeniskb.kode 
					where 
					bea_pkb_pok > 0

					and tg_tetap >= '01-01-$periode' 
					and tg_tetap <= '31-12-$periode'  
					";
		if (($kode_wil !='all'))
			{$query .= "and t_trnkb.kd_wilayah='$kode_wil'";}
		if (($jenis_kb !='all'))
			{$query .= "and t_trnkb.kd_jenis_kb='$jenis_kb'";}	
			$query	.="group by kd_wilayah,kd_jenis_kb,nm_wilayah,uraian";
		$this->db->flush_cache();
		echo $query;
		
		return $this->db->count_all_results($query);
	}
	 
	function piutang($periode=0, $kode_wil=null, $jenis_kb=null)
	{ 
	
	if (!$periode){$periode = 0;}
	if (!$kode_wil){$kode_wil = 0;}
	if (!$jenis_kb){$jenis_kb = 0;}
		$query = "select   t_trnkb.kd_wilayah,
					t_wilayah.nm_wilayah,
					t_trnkb.kd_jenis_kb,
					t_jeniskb.uraian,
					sum(bea_pkb_pok) as piutang 
					from t_trnkb 
					left join t_wilayah on t_trnkb.kd_wilayah = t_wilayah.kd_wilayah  
					left join t_jeniskb on  t_trnkb.kd_jenis_kb = t_jeniskb.kode 
					where 
					bea_pkb_pok > 0

					and tg_tetap >= '01-01-$periode' 
					and tg_tetap <= '31-12-$periode'  
					 
					  
					";
		if (($kode_wil !='all'))
			{$query .= "and t_trnkb.kd_wilayah='$kode_wil'";}
		if (($jenis_kb !='all'))
			{$query .= "and t_trnkb.kd_jenis_kb='$jenis_kb'";}	 
			$query	.="group by t_trnkb.kd_wilayah,kd_jenis_kb,nm_wilayah,uraian";
		$this->db->flush_cache();
		echo $query;echo'------';
		return $this->db->query($query);
	}


	function sudah_bayar($periode=0, $kode_wil=null, $jenis_kb=null)
	{ 
	
	if (!$periode){$periode = 0;}
	if (!$kode_wil){$kode_wil = 0;}
	if (!$jenis_kb){$jenis_kb = 0;}
		$query = "select  t_trnkb.kd_wilayah,
					t_wilayah.nm_wilayah,
					t_trnkb.kd_jenis_kb,
					t_jeniskb.uraian,
					sum(bea_pkb_pok) as sudah_bayar 
					from t_trnkb 
					left join t_wilayah on t_trnkb.kd_wilayah = t_wilayah.kd_wilayah  
					left join t_jeniskb on  t_trnkb.kd_jenis_kb = t_jeniskb.kode 
					where 
					bea_pkb_pok > 0

					and tg_tetap >= '01-01-$periode' 
					and tg_tetap <= '31-12-$periode'  

					and tg_bayar >= '01-01-$periode' 
					and tg_bayar <= '31-12-$periode'  
					  
					";
		if (($kode_wil !='all'))
			{$query .= "and t_trnkb.kd_wilayah='$kode_wil'";}
		if (($jenis_kb !='all'))
			{$query .= "and t_trnkb.kd_jenis_kb='$jenis_kb'";}	
			$query	.="group by t_trnkb.kd_wilayah,kd_jenis_kb,nm_wilayah,uraian";
		$this->db->flush_cache();
		echo $query;echo'------';
		return $this->db->query($query);
	}


	
	function getKodeWil()
	{
		$query = "select * from t_wilayah order by  kd_wilayah asc";
		$this->db->flush_cache();
		return $this->db->query($query);
	}
	
	
	function getJenisKB()
	{
		$query = "select * from t_jeniskb order by kode";
		$this->db->flush_cache();
		return $this->db->query($query);
	}
	
	function getKodeWilValue($id)
	{
		$query = "select nm_wilayah from t_wilayah where kd_wilayah = '$id'";
		$this->db->flush_cache();
		$result = $this->db->query($query);
		return $result->result_array();	
	}
	
	function getJenisKBValue($id)
	{
		$query = "select uraian from t_jeniskb where kode = '$id'";
		$this->db->flush_cache();
		$result = $this->db->query($query);
		return $result->result_array();	
	}
	
	function getItem_kas_awal($id)
	{
		$query = "SELECT 
					Sum(t_trnkb.bea_pkb_pok) AS pkb
					FROM
					t_trnkb
					left JOIN t_wilayah ON t_wilayah.kd_wilayah = t_trnkb.kd_wilayah
					left JOIN t_jeniskb ON t_trnkb.kd_jenis_kb = t_jeniskb.kode
					where tg_akhir_pkb <= '$id-12-31' and YEAR(tg_akhir_pkb)='$id'";
		$this->db->flush_cache();
		$result = $this->db->query($query);
		return $result->result_array();	
	}

}